﻿Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports Tamir.SharpSsh
Imports log4net
Imports System.Threading
Imports System.Globalization
Imports System.Windows.Forms

Partial Class ExportFrm_Pivot
    Inherits System.Web.UI.Page

    Public scirpttext As String
    Private Shared logger As ILog = LogManager.GetLogger("ExportFrm_Pivot")

    Protected Overrides Sub InitializeCulture()
        Dim selectedLanguage As String = "en-US"
        Thread.CurrentThread.CurrentCulture = _
            CultureInfo.CreateSpecificCulture(selectedLanguage)
        Thread.CurrentThread.CurrentUICulture = New  _
            CultureInfo(selectedLanguage)

        MyBase.InitializeCulture()
    End Sub

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            Response.Redirect("LoginForm.aspx")
        End If

        'Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("th-TH")
        'Thread.CurrentThread.CurrentUICulture = New CultureInfo("th-TH")

        PlanYearsTxt.Text = Session("PlanYear")
        Label1.Text = "ระบบจัดทำงบประมาณ > จัดทำงบประมาณ > Export Pivot Data"
    End Sub

    Sub httpDownload(ByVal _expText As String)
        Dim success As Boolean = False

        logger.Info("User " & Session("users") & " start download file.")

        Try
            Dim fileName As String = "Pivot_"
            If String.IsNullOrEmpty(VersionTxt.Text) Then
                filename &= PlanYearsTxt.Text
            Else
                filename &= PlanYearsTxt.Text & "_" & VersionTxt.Text
            End If

            'Dim fullName As String = Server.MapPath("Export") & "\" & fileName

            'Using writer As StreamWriter = New StreamWriter(fullName, False, System.Text.Encoding.GetEncoding(874))
            '    writer.Write(_expText)
            'End Using

            Response.Clear()
            Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".txt")
            Response.Buffer = True
            Response.Charset = "UTF-8"
            'set encoding as Unicode due to cannot read Thai characters on SAP system
            Response.ContentEncoding = Encoding.UTF8
            'Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = "application/vnd.text"

            'Dim stringWrite As New System.IO.StringWriter()
            'Dim htmlWrite = New HtmlTextWriter(stringWrite)

            Response.Write(_expText)

            'Response.Clear()
            'Response.AppendHeader("content-disposition", "attachment;filename=" & filename & ".txt")
            'Response.Buffer = True
            'Response.Charset = "windows-874"
            'Response.ContentEncoding = System.Text.Encoding.Default
            'Response.ContentType = "Application/vnd.text"
            'Response.Write(_expText)

            success = True

            logger.Info("User " & Session("users") & " download file successfully.")
            Response.End()
        Catch ex As Exception
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)

            success = False

            logger.Info("User " & Session("users") & " download file failed.")
        End Try

        'If success = True Then
        '	scirpttext = "<script>alert('ส่งออกไฟล์สำเร็จ')</script>"
        'Else
        '	scirpttext = "<script>alert('พบปัญหาในการโอนถ่ายไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
        'End If
    End Sub

    Protected Sub SearhBtn_Click(sender As Object, e As EventArgs) Handles SearhBtn.Click
        Dim ExportText As String = ""
        Dim bd_adapter As New ExportTableAdapters.BudgetDetail_2TableAdapter
        Dim BD_datatable As New Export.BudgetDetail_2DataTable

        logger.Info("User " & Session("users") & " start query pivot data.")

        bd_adapter.SetCommandTimeOut()
        If String.IsNullOrEmpty(VersionTxt.Text) Then
            BD_datatable = bd_adapter.GetDataByYear(PlanYearsTxt.Text)
        Else
            BD_datatable = bd_adapter.GetData(VersionTxt.Text, PlanYearsTxt.Text)
        End If
        logger.Info("User " & Session("users") & " query pivot data successfully.")

        logger.Info("User " & Session("users") & " start export pivot file.")
        Dim comma As String = ","

        'ExportText += "PlanYear" & comma & "Version" & comma & "ResponsibleAnalystCode" & comma
        'ExportText += "ResponsibleAnalystName" & comma & "FundCenterCode" & comma & "FundCenterDescription" & comma & "FundCenterTypeCode" & comma
        'ExportText += "FundCenterTypeDescription" & comma & "FundCenterLevel" & comma & "StrategyCode" & comma
        'ExportText += "ActivityCode" & comma & "ActivityTypeCode" & comma & "AssetGroup" & comma & "StandardPrice" & comma
        'ExportText += "PricePerUnit" & comma & "NewQuantity" & comma & "ReplaceQuantity" & comma & "TotalQuantity" & comma
        'ExportText += "BudgetAmount" & comma & "CmmtCode" & comma & "CmmtDescription" & comma & "CmmtDescription2" & comma
        'ExportText += "CmmtDescription3" & comma & "BusinessAreaCode" & comma & "ProfitCenterCode" & comma & "ProductCode" & comma
        'ExportText += "ProductDescription" & comma & "EvmCode" & comma & "EvmDescription" & comma
        'ExportText += "ActivityTypeGroup" & comma & "ProjectGroup" & comma & "ProjectCode" & comma & "ProjectDescription" & Chr(13) & Chr(10)


        'For Each bd_row As Export.BudgetDetail_2Row In BD_datatable
        '    With bd_row
        '        ExportText += .PlanYear & comma & .Version & comma _
        '            & If(.IsResponsibleAnalystNameNull, "", .ResponsibleAnalystCode) & comma _
        '            & If(.IsResponsibleAnalystNameNull, "", .ResponsibleAnalystName) & comma _
        '            & If(.IsFundCenterCodeNull, "", .FundCenterCode) & comma _
        '            & If(.IsFundCenterDescriptionNull, "", .FundCenterDescription) & comma _
        '            & If(.IsFundCenterTypeCodeNull, "", .FundCenterTypeCode) & comma _
        '            & If(.IsFundCenterTypeDescriptionNull, "", .FundCenterTypeDescription) & comma _
        '            & If(.IsFundCenterLevelNull, "", CStr(.FundCenterLevel)) & comma _
        '            & If(.IsStrategyCodeNull, "", .StrategyCode) & comma _
        '            & If(.IsActivityCodeNull, "", .ActivityCode) & comma _
        '            & If(.IsActivityTypeCodeNull, "", .ActivityTypeCode) & comma _
        '            & If(.IsAssetGroupNull, 0, .AssetGroup) & comma _
        '            & Format((If(.IsStandardPriceNull, 0, .StandardPrice)), "#.00") & comma _
        '            & Format((If(.IsPricePerUnitNull, 0, .PricePerUnit)), "#.00") & comma _
        '            & If(.IsNewQuantityNull, 0, .NewQuantity) & comma _
        '            & If(.IsReplaceQuantityNull, 0, .ReplaceQuantity) & comma _
        '            & If(.IsTotalQuantityNull, 0, .TotalQuantity) & comma _
        '            & Format((If(.IsBudgetAmountNull, 0, .BudgetAmount)), "#.00") & comma _
        '            & If(.IsCmmtCodeNull, "", .CmmtCode) & comma _
        '            & If(.IsCmmtDescriptionNull, "", .CmmtDescription) & comma _
        '            & If(.IsCmmtDescription2Null, "", .CmmtDescription2) & comma _
        '            & If(.IsCmmtDescription3Null, "", .CmmtDescription3) & comma _
        '            & If(.IsBusinessAreaCodeNull, "", .BusinessAreaCode) & comma _
        '            & If(.IsProfitCenterCodeNull, "", .ProfitCenterCode) & comma _
        '            & If(.IsProductCodeNull, "", .ProductCode) & comma _
        '            & If(.IsProductDescriptionNull, "", .ProductDescription) & comma _
        '            & If(.IsEvmCodeNull, "", .EvmCode) & comma _
        '            & If(.IsEvmDescriptionNull, "", .EvmDescription) & comma _
        '            & If(.IsActivityTypeGroupNull, "", .ActivityTypeGroup) & comma _
        '            & If(.IsProjectGroupNull, "", .ProjectGroup) & comma _
        '            & If(.IsProjectCodeNull, "", .ProjectCode) & comma _
        '            & If(.IsProjectDescriptionNull, "", .ProjectDescription) _
        '            & Chr(13) & Chr(10)
        '    End With
        'Next

        Dim oStrBuilder = New StringBuilder()
        oStrBuilder.Append("PlanYear" + comma + "Version" + comma + "ResponsibleAnalystCode" + comma)
        oStrBuilder.Append("ResponsibleAnalystName" + comma + "FundCenterCode" + comma + "FundCenterDescription" + comma + "FundCenterTypeCode" + comma)
        oStrBuilder.Append("FundCenterTypeDescription" + comma + "FundCenterLevel" + comma + "StrategyCode" + comma)
        oStrBuilder.Append("ActivityCode" + comma + "ActivityTypeCode" + comma + "AssetGroup" + comma + "StandardPrice" + comma)
        oStrBuilder.Append("PricePerUnit" + comma + "NewQuantity" + comma + "ReplaceQuantity" + comma + "TotalQuantity" + comma)
        oStrBuilder.Append("BudgetAmount" + comma + "CmmtCode" + comma + "CmmtDescription" + comma + "CmmtDescription2" + comma)
        oStrBuilder.Append("CmmtDescription3" + comma + "BusinessAreaCode" + comma + "ProfitCenterCode" + comma + "ProductCode" + comma)
        oStrBuilder.Append("ProductDescription" + comma + "EvmCode" + comma + "EvmDescription" + comma)
        oStrBuilder.Append("ActivityTypeGroup" + comma + "ProjectGroup" + comma + "ProjectCode" + comma + "ProjectDescription" + comma)
        oStrBuilder.AppendLine("ActivityTypeDescription" + comma + "AssetGroupDescription")

        For Each bd_row As Export.BudgetDetail_2Row In BD_datatable
            With bd_row
                oStrBuilder.Append(.PlanYear.ToString + comma + .Version + comma)
                oStrBuilder.Append(If(.IsResponsibleAnalystNameNull, "", .ResponsibleAnalystCode) + comma)
                oStrBuilder.Append(If(.IsResponsibleAnalystNameNull, "", .ResponsibleAnalystName) + comma)
                oStrBuilder.Append(If(.IsFundCenterCodeNull, "", .FundCenterCode) + comma)
                oStrBuilder.Append(If(.IsFundCenterDescriptionNull, "", .FundCenterDescription) + comma)
                oStrBuilder.Append(If(.IsFundCenterTypeCodeNull, "", .FundCenterTypeCode) + comma)
                oStrBuilder.Append(If(.IsFundCenterTypeDescriptionNull, "", .FundCenterTypeDescription) + comma)
                oStrBuilder.Append(If(.IsFundCenterLevelNull, "", CStr(.FundCenterLevel)) + comma)
                oStrBuilder.Append(If(.IsStrategyCodeNull, "", .StrategyCode) + comma)
                oStrBuilder.Append(If(.IsActivityCodeNull, "", .ActivityCode) + comma)
                oStrBuilder.Append(If(.IsActivityTypeCodeNull, "", .ActivityTypeCode) + comma)
                oStrBuilder.Append(Str(If(.IsAssetGroupNull, 0, .AssetGroup)) + comma)
                oStrBuilder.Append(Format((If(.IsStandardPriceNull, 0, .StandardPrice)), "#.00") + comma)
                oStrBuilder.Append(Format((If(.IsPricePerUnitNull, 0, .PricePerUnit)), "#.00") + comma)
                oStrBuilder.Append(Format((If(.IsNewQuantityNull, 0, .NewQuantity)), "#.00") + comma)
                oStrBuilder.Append(Format((If(.IsReplaceQuantityNull, 0, .ReplaceQuantity)), "#.00") & comma)
                oStrBuilder.Append(Format(If(.IsTotalQuantityNull, 0, .TotalQuantity)) + comma)
                oStrBuilder.Append(Format((If(.IsBudgetAmountNull, 0, .BudgetAmount)), "#.00") + comma)
                oStrBuilder.Append(If(.IsCmmtCodeNull, "", .CmmtCode) + comma)
                oStrBuilder.Append(If(.IsCmmtDescriptionNull, "", .CmmtDescription) + comma)
                oStrBuilder.Append(If(.IsCmmtDescription2Null, "", .CmmtDescription2) + comma)
                oStrBuilder.Append(If(.IsCmmtDescription3Null, "", .CmmtDescription3) + comma)
                oStrBuilder.Append(If(.IsBusinessAreaCodeNull, "", .BusinessAreaCode) + comma)
                oStrBuilder.Append(If(.IsProfitCenterCodeNull, "", .ProfitCenterCode) + comma)
                oStrBuilder.Append(If(.IsProductCodeNull, "", .ProductCode) + comma)
                oStrBuilder.Append(If(.IsProductDescriptionNull, "", .ProductDescription) + comma)
                oStrBuilder.Append(If(.IsEvmCodeNull, "", .EvmCode) + comma)
                oStrBuilder.Append(If(.IsEvmDescriptionNull, "", .EvmDescription) + comma)
                oStrBuilder.Append(If(.IsActivityTypeGroupNull, "", .ActivityTypeGroup) + comma)
                oStrBuilder.Append(If(.IsProjectGroupNull, "", .ProjectGroup) + comma)
                oStrBuilder.Append(If(.IsProjectCodeNull, "", .ProjectCode) + comma)
                oStrBuilder.Append(If(.IsProjectDescriptionNull, "", .ProjectDescription) + comma)
                oStrBuilder.Append(If(.IsActivityTypeDescriptionNull, "", .ActivityTypeDescription) + comma)
                oStrBuilder.AppendLine(If(.IsAssetGroupDescriptionNull, "", .AssetGroupDescription))
            End With
        Next

        logger.Info("User " & Session("users") & " export pivot file successfully.")

        Try
            httpDownload(oStrBuilder.ToString())
        Catch ex As Exception
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)

            scirpttext = "<script>alert('พบปัญหาในการสร้างไฟล์ ไม่สามารถส่งออกไฟล์ได้')</script>"
            Exit Sub
        End Try
    End Sub

    Protected Sub DownloadButton_Click(sender As Object, e As EventArgs) Handles DownloadButton.Click
        Dim filename As String = "Pivot_Template.zip"
        Dim path As String = Server.MapPath("~") + "\Download\" + filename
        Dim File As FileInfo = New System.IO.FileInfo(path)

        'If (File.Exists) Then
        Response.Clear()
        Response.AddHeader("Content-Disposition", "attachment; filename=" + File.Name)
        Response.AddHeader("Content-Length", File.Length.ToString())
        Response.ContentType = "application/octet-stream"
        Response.WriteFile(File.FullName)
        Response.End()
        'Else
        'Response.Write("This file does not exist.")
        'End If
    End Sub
End Class
